package com.cxhome.cxinfox.report;

import java.util.List;
import java.util.Map;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import com.traffic.managent.annotation.ModelBind;
import com.traffic.managent.base.SessionUser;

@ModelBind(table = "cx_day_report", key = "ids")
public class DayReport extends SessionUser<DayReport> {

	private static final long serialVersionUID = 1L;
	public static final DayReport dao = new DayReport();

	/**
	 * 获取每天的账单信息
	 * 
	 * @param date
	 * @return
	 */
	public int saveCusAndOper(String date, String createBy) {
		String sql = "INSERT INTO cx_day_report (  customerId,  operator,  reportDate, "
				+ " createTime,  createBy  ) SELECT  t.customerId,  t.operator, "
				+ " DATE_FORMAT(t.orderTime, '%Y-%m-%d') reportDate,  now(),  ?  FROM  cx_order t "
				+ " WHERE  DATE_FORMAT(t.orderTime, '%Y-%m-%d') =?  GROUP BY "
				+ " DATE_FORMAT(t.orderTime, '%Y-%m-%d'),  	t.customerId,  t.operator ";
		return Db.update(sql, createBy, date);
	}

	/**
	 * 更新成条数
	 * 
	 * @param date
	 * @return
	 */
	public int updateSucceed(String date) {
		String sql = "UPDATE cx_day_report b  SET b.succeed = (  SELECT  	count(t.ids)  FROM "
				+ " 	cx_order t  WHERE  	DATE_FORMAT(t.orderTime, '%Y-%m-%d') = b.reportDate "
				+ " AND t.operator = b.operator  AND t.customerId = b.customerId  AND t. STATUS = '7' "
				+ " )  where b.reportDate =? ";
		return Db.update(sql, date);
	}

	/**
	 * 更新交易额
	 * 
	 * @param date
	 * @return
	 */
	public int updateTradeAmount(String date) {
		String sql = "UPDATE cx_day_report b  SET b.tradeAmount = (  	SELECT "
				+ " 	case when sum(t.sellPrice) is null then 0 else sum(t.sellPrice) end  FROM "
				+ " 	cx_order t  WHERE  	DATE_FORMAT(t.orderTime, '%Y-%m-%d') = b.reportDate "
				+ " AND t.operator = b.operator  AND t.customerId = b.customerId  AND t. STATUS = '7' "
				+ " ),  b.tradeAmount2 = (  SELECT "
				+ " 	case when sum(t.orderPrice) is null then 0 else sum(t.orderPrice) end  	FROM "
				+ " 	cx_order t  	WHERE  	DATE_FORMAT(t.orderTime, '%Y-%m-%d') = b.reportDate "
				+ " AND t.operator = b.operator  AND t.customerId = b.customerId  AND t. STATUS = '7' "
				+ " )  where b.reportDate =? ";
		return Db.update(sql, date);
	}

	/**
	 * 更新失败条数
	 * 
	 * @param date
	 * @return
	 */
	public int updateFailed(String date) {
		String sql = "	UPDATE cx_day_report b  SET b.failed = (  SELECT  count(t.ids)  FROM "
				+ " cx_order t  WHERE  DATE_FORMAT(t.orderTime, '%Y-%m-%d') = b.reportDate "
				+ " AND t.operator = b.operator  AND t.customerId = b.customerId  AND t. STATUS = '8' "
				+ " 	)  where b.reportDate =? ";
		return Db.update(sql, date);
	}

	/**
	 * 更新成功率
	 * 
	 * @param date
	 * @return
	 */
	public int updateSucceedRate(String date) {
		String sql = "UPDATE cx_day_report b "
				+ " SET b.successRate = (case when b.succeed+b.failed>0 then (b.succeed/(b.succeed+b.failed))*100 else 0 end) "
				+ " where b.reportDate =? ";
		return Db.update(sql, date);
	}

	/**
	 * 更新成功率
	 * 
	 * @param date
	 * @return
	 */
	public int updateAllCount(String date) {
		String sql = "UPDATE cx_day_report b  SET b.allCount = (  SELECT  count(t.ids)  FROM "
				+ " cx_order t  WHERE  DATE_FORMAT(t.orderTime, '%Y-%m-%d') = b.reportDate "
				+ " AND t.operator = b.operator  AND t.customerId = b.customerId  	) "
				+ " where b.reportDate =? ";
		return Db.update(sql, date);
	}

	/**
	 * 判断该时间账单是否已生成
	 * 
	 * @param date
	 * @return
	 */
	public boolean isExistByDate(String date) {
		String sql = "select count(*) count from  "
				+ " (SELECT DISTINCT customerId,reportDate,operator,succeed,successRate,failed,tradeAmount,tradeAmount2,withdraw,drawAmount,createTime,allCount,createBy FROM cx_day_report GROUP BY customerId,reportDate) b  "
				+ " where b.reportDate =? ";
		return dao.findCount(sql, date) > 0;
	}

	/**
	 * 根据时间和运营商分组统计
	 * 
	 * @param params
	 * @return
	 */
	public List<Record> sumByDateAndOper(Map<String, Object> params) {
		String sql = "SELECT  sum(b.succeed) succeed,  sum(b.failed) failed, "
				+ " sum(b.tradeAmount) tradeAmount,  sum(b.tradeAmount2) tradeAmount2, "
				+ " sum(b.allCount) allCount,  sum(b.withdraw) withdraw,  sum(b.drawAmount) drawAmount, "
				+ " CASE  WHEN b.operator = '1' THEN  	'电信'  WHEN b.operator = '2' THEN "
				+ " 	'移动'  ELSE  	'联通'  END operatorName,  '汇总' customerName, "
				+ " '' reportDate,  b.operator  FROM "
				+ " 	(SELECT DISTINCT customerId,reportDate,operator,succeed,successRate,failed,tradeAmount,tradeAmount2,withdraw,drawAmount,createTime,allCount,createBy FROM cx_day_report GROUP BY customerId,reportDate,operator) b , "
				+ "  cx_customer_info m , cx_user u   "
				+ " WHERE b.customerId = m.customerId  and u.customerId = m.customerId ";

		if (params.get("startDate") != null) {
			sql += "and  b.reportDate >= '" + params.get("startDate") + "'";
		}
		if (params.get("endDate") != null) {
			sql += "and  b.reportDate <= '" + params.get("endDate") + "'";
		}
		if (params.get("customerId") != null) {
			sql += "and  b.customerId = '" + params.get("customerId") + "'";
		}
		if (params.get("departmentId") != null) {
			sql += "and  u.departmentId in " + params.get("departmentId");
		}
		sql += "group by b.operator order by b.operator ";

		return Db.find(sql);
	}

	/**
	 * 根据条件合计
	 * 
	 * @param params
	 * @return
	 */
	public Record sumByDate(Map<String, Object> params) {
		String sql = "SELECT  sum(b.succeed) succeed,  sum(b.failed) failed, "
				+ " sum(b.tradeAmount) tradeAmount,  sum(b.tradeAmount2) tradeAmount2, "
				+ " sum(b.allCount) allCount,  sum(b.withdraw) withdraw,  sum(b.drawAmount) drawAmount, "
				+ " '三网' operatorName,   '合计' customerName  FROM "
				+ " (SELECT DISTINCT customerId,reportDate,operator,succeed,successRate,failed,tradeAmount,tradeAmount2,withdraw,drawAmount,createTime,allCount,createBy "
				+ "FROM cx_day_report GROUP BY customerId,reportDate,operator) b , "
				+ " 	 cx_customer_info m, cx_user u   "
				+ " WHERE b.customerId = m.customerId and u.customerId = m.customerId  ";
		if (params.get("startDate") != null) {
			sql += "and  b.reportDate >= '" + params.get("startDate") + "'";
		}
		if (params.get("endDate") != null) {
			sql += "and  b.reportDate <= '" + params.get("endDate") + "'";
		}
		if (params.get("customerId") != null) {
			sql += "and  b.customerId = '" + params.get("customerId") + "'";
		}
		if (params.get("departmentId") != null) {
			sql += "and  u.departmentId in " + params.get("departmentId");
		}
		return Db.findFirst(sql);
	}

	public List<Record> listReport(Map<String, Object> params) {
		String sql = "SELECT b.*, case WHEN b.successRate is not null THEN b.successRate ELSE 0 END suRate,case "
				+ "WHEN b.operator = '1' THEN '电信' WHEN b.operator = '2' THEN '移动' ELSE '联通' END operatorName "
				+ " FROM  ( "
				+ " SELECT o.customerId,o.operator,SUM(o.allCount) allCount ,SUM(o.succeed) succeed,SUM(o.failed) failed, "
				+ "SUM(o.tradeAmount) tradeAmount,SUM(o.tradeAmount2) tradeAmount2,sum(o.withdraw) withdraw,sum(o.drawAmount) drawAmount, "
				+ " (CASE WHEN SUM(o.succeed)+SUM(o.failed)>0 THEN (SUM(o.succeed)/(SUM(o.succeed)+SUM(o.failed)))*100 ELSE 0 END) AS successRate , ci.customerName customerName  "
				+ " FROM  "
				+ " (SELECT customerId,reportDate,operator,succeed,successRate,failed,tradeAmount,tradeAmount2,withdraw,drawAmount,createTime,allCount,createBy "
				+ "FROM cx_day_report GROUP BY customerId,reportDate,operator) o, "
				+ " cx_customer_info ci , cx_user u   "
				+ " WHERE o.customerId = ci.customerId and u.customerId = ci.customerId ";

		if (params.get("startDate") != null) {
			sql += "and  o.reportDate >= '" + params.get("startDate") + "'";
		}
		if (params.get("endDate") != null) {
			sql += "and  o.reportDate <= '" + params.get("endDate") + "'";
		}
		if (params.get("customerId") != null) {
			sql += "and  o.customerId = '" + params.get("customerId") + "'";
		}
		if (params.get("departmentId") != null) {
			sql += "and  u.departmentId in " + params.get("departmentId");
		}

		sql += " GROUP BY o.customerId,o.operator ) b, "

				+ " (select sum(t.tradeAmount) sumAmount,t.customerId  "
				+ "    from (SELECT DISTINCT customerId,reportDate,operator,succeed,successRate,failed,tradeAmount,tradeAmount2,withdraw,drawAmount,createTime,allCount,createBy "
				+ "FROM cx_day_report GROUP BY customerId,reportDate,operator) t , "
				+ " 	    cx_customer_info m, cx_user u  "
				+ " where t.customerId = m.customerId  and u.customerId = m.customerId ";

		if (params.get("startDate1") != null) {
			sql += "and  t.reportDate >= '" + params.get("startDate1") + "'";
		}
		if (params.get("endDate1") != null) {
			sql += "and  t.reportDate <= '" + params.get("endDate1") + "'";
		}
		if (params.get("customerId1") != null) {
			sql += "and  t.customerId = '" + params.get("customerId1") + "'";
		}
		if (params.get("departmentId") != null) {
			sql += "and  u.departmentId in " + params.get("departmentId");
		}

		sql += "  group by t.customerId) a WHERE a.customerId=b.customerId order by a.sumAmount desc,b.customerId,b.operator";

		return Db.find(sql);
	}
}
